package com.lanou.bookstore.book.dao;

import com.lanou.bookstore.book.domain.Book;
import com.lanou.bookstore.utils.C3p0_utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookDao {
    public List<Book> findAll() throws SQLException {
        Connection connection = C3p0_utils.getConnection();
        String sql = "select * from book where del = 0";
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet resultSet = ps.executeQuery();
        List<Book> bookList = new ArrayList<>();
        while (resultSet.next()) {
            Book book = new Book();
            book.setBid(resultSet.getInt("bid"));
            book.setBname(resultSet.getString("bname"));
            book.setPrice(resultSet.getDouble("price"));
            book.setAuthor(resultSet.getString("author"));
            book.setImage(resultSet.getString("image"));
            book.setCid(resultSet.getString("cid"));
            bookList.add(book);
        }
        return bookList;
    }

    public List<Book> findByCid(String cid) throws SQLException {
        Connection connection = C3p0_utils.getConnection();
        String sql = "select * from book where cid = ? and del = 0";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, cid);
        ResultSet resultSet = ps.executeQuery();
        List<Book> bookList = new ArrayList<>();
        while (resultSet.next()) {
            Book book = new Book();
            book.setBid(resultSet.getInt("bid"));
            book.setBname(resultSet.getString("bname"));
            book.setPrice(resultSet.getDouble("price"));
            book.setAuthor(resultSet.getString("author"));
            book.setImage(resultSet.getString("image"));
            book.setCid(resultSet.getString("cid"));
            bookList.add(book);
        }
        return bookList;
    }

    public Book findByBid(String bid) throws SQLException {
        Connection connection = C3p0_utils.getConnection();
        String sql = "select * from book where bid = ? and del = 0";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, bid);
        ResultSet resultSet = ps.executeQuery();
        if (resultSet.next()) {
            Book book = new Book();
            book.setBid(resultSet.getInt("bid"));
            book.setBname(resultSet.getString("bname"));
            book.setPrice(resultSet.getDouble("price"));
            book.setAuthor(resultSet.getString("author"));
            book.setImage(resultSet.getString("image"));
            book.setCid(resultSet.getString("cid"));
            return book;
        } else {
            return null;
        }
    }

    public void updataDelByBid(Integer bid) throws SQLException {
        Connection connection = C3p0_utils.getConnection();
        String sql = "update book set del = 1 where bid = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, bid);
        ps.executeUpdate();
    }

    public void updateAll(Book book) throws SQLException {
        Connection connection = C3p0_utils.getConnection();
        String sql = "update book set bname = ? , price = ? , author = ? , cid = ? where bid = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, book.getBname());
        ps.setDouble(2, book.getPrice());
        ps.setString(3, book.getAuthor());
        ps.setString(4, book.getCid());
        ps.setInt(5, book.getBid());
        ps.executeUpdate();
    }

    public void addBook(Book book) throws SQLException {
        Connection connection = C3p0_utils.getConnection();
        String sql = "insert into book values(null,?,?,?,?,?,0)";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, book.getBname());
        ps.setDouble(2, book.getPrice());
        ps.setString(3, book.getAuthor());
        ps.setString(4, book.getImage());
        ps.setInt(5, Integer.parseInt(book.getCid()));
        ps.executeUpdate();
    }
}
